package com.bitcat.firstapp.app.db

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper

class DataBaseHelper(
    context: Context?,
) : SQLiteOpenHelper(context, NAME, null, VERSION) {


    companion object {
        private val NAME = "todo.db"
        private val VERSION = 1

        // 用户表和表的字段
        val TABLE_USER = "user"
        val COLUMN_USER_ID = "id"
        val COLUMN_USER_NAME = "name"
        val COLUMN_USER_PASSWORD = "password"

        //建表语句
        val sql =
            "CREATE TABLE $TABLE_USER ($COLUMN_USER_ID INTEGER PRIMARY KEY AUTOINCREMENT, $COLUMN_USER_NAME TEXT UNIQUE, $COLUMN_USER_PASSWORD TEXT)"
    }

    override fun onCreate(db: SQLiteDatabase?) {
        // 创建用户表
        db?.execSQL(sql)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //升级策略：给表增加一个字段：年龄
        if (oldVersion < newVersion) {
            db?.execSQL("ALTER TABLE $TABLE_USER ADD COLUMN age INTEGER")
        }
    }


    //注册用户
    fun registerUser(name: String, password: String): Boolean {
        // 获取数据库
        val db = writableDatabase
        // 创建ContentValues对象，用于封装要插入的数据
        val values = ContentValues()
        values.put(COLUMN_USER_NAME, name)
        values.put(COLUMN_USER_PASSWORD, password)

        // 执行插入操作，返回插入成功的行数
        val result = db.insert(TABLE_USER, null, values)
//        db.close()

        // 判断是否插入成功 返回-1L表示插入失败
        return result != -1L
    }

    //判断用户是否已经注册
    fun checkUserExist(name: String): Boolean {
        // 获取数据库
        val db = readableDatabase
        // 查询用户名是否存在
        val cursor = db.query(
            TABLE_USER,
            arrayOf(COLUMN_USER_NAME),
            "$COLUMN_USER_NAME = ?",
            arrayOf(name),
            null,
            null,
            null
        )
        val isRegistered = cursor.count > 0
        cursor.close()
//        db.close()
        return isRegistered
    }

    //删除某个用户
    fun deleteUser(name: String): String {
       //先查询是否存在，存在才删除
        if (checkUserExist(name)) {
            // 获取数据库
            val db = writableDatabase
            // 执行删除操作，返回删除成功的行数
            val result = db.delete(TABLE_USER, "$COLUMN_USER_NAME = ?", arrayOf(name))
//            db.close()
            return if (result > 0) {
                "删除成功"
            } else {
                "删除失败"
            }
        } else {
            return "用户不存在"
        }
    }

    //更新某个用户信息
    fun updateUser(name: String, password: String): String {
//        val db = writableDatabase
//
//        try {//先查询是否存在，存在才更新,编写SQL，直接执行sql
//            val sql = "UPDATE $TABLE_USER SET $COLUMN_USER_PASSWORD = '$password' WHERE $COLUMN_USER_NAME = '$name'"
//            db.execSQL(sql)
//        } catch (e: Exception) {
//            return "更新失败:" + e.toString()
//        } finally {
////            db.close()
//        }


        if (checkUserExist(name)) {
            // 获取数据库
            val db = writableDatabase
            // 创建ContentValues对象，用于封装要更新的数据
            val values = ContentValues()
            values.put(COLUMN_USER_PASSWORD, password)
            // 执行更新操作，返回更新成功的行数
            val result = db.update(TABLE_USER, values, "$COLUMN_USER_NAME = ?", arrayOf(name))
            return if (result > 0) {
                "更新成功"
            } else {
                "更新失败"
            }
        }else{
            return "用户不存在"
        }
    }


    //查询所有用户
    @SuppressLint("Range")
    fun queryAllUsers(): List<User> {
        val db = readableDatabase
        val cursor = db.query(
            TABLE_USER,
            arrayOf(COLUMN_USER_ID, COLUMN_USER_NAME, COLUMN_USER_PASSWORD),
            null,
            null,
            null,
            null,
            null
        )

        // 遍历游标，将查询结果封装为User对象并添加到列表中
        val users = mutableListOf<User>()
        while (cursor.moveToNext()) {
            val id = cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID))
            val name = cursor.getString(cursor.getColumnIndex(COLUMN_USER_NAME))
            val password = cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD))
            val user = User(id, name, password)
            users.add(user)
        }

        cursor.close()
        return users
    }


    //登录
    fun login(name:String,password:String):Boolean{
        //获取数据库db
        val db = readableDatabase

        //查询用户名是否存在
        val cursor = db.query(
            TABLE_USER,
            arrayOf(COLUMN_USER_ID),
            "$COLUMN_USER_NAME = ? and $COLUMN_USER_PASSWORD = ?",
            arrayOf(name,password),
            null,null,null
        )

        val successFull = cursor.count > 0


        //关闭cursor
        cursor.close()

        return successFull
    }






}